CREATE procedure sp_asi_UpdateTransSoftCredit
as
set nocount on
declare
@TransNumber int,
@BtId varchar(10),
@StId varchar(10),
@Amount money,
@TotalAmount money,
@DistAmount money,
@ActivitySeqn int,
@Percent float,
@TotalPercent float,
@count int,
@ProductCode varchar(31),
@InvRefNum int,
@JournalType varchar(5)
create table #SplitGiftRows (
TransNumber int,
StId varchar(10),
Amount money,
ActivitySeqn int,
ProductCode varchar(31)
)
select @Percent=100
declare Get_Trans cursor for
select TRANS_NUMBER,max(BT_ID),max(ST_ID),-max(AMOUNT),-sum(AMOUNT),max(ACTIVITY_SEQN),max(PRODUCT_CODE),
max(INVOICE_REFERENCE_NUM),max(JOURNAL_TYPE)
from Trans where TRANSACTION_TYPE='DIST' and SOURCE_SYSTEM='FR' and BT_ID<>ST_ID
group by TRANS_NUMBER
open Get_Trans
fetch next from Get_Trans into @TransNumber,@BtId,@StId,@Amount,@TotalAmount,@ActivitySeqn,@ProductCode,@InvRefNum,@JournalType
WHILE @@FETCH_STATUS = 0
BEGIN
if @TotalAmount=@Amount
BEGIN
if @JournalType in ('CM','DM')
BEGIN
select @ActivitySeqn=max(ACTIVITY_SEQN) from Trans
join Invoice on REFERENCE_NUM=@InvRefNum
where TRANS_NUMBER=ORIGINATING_TRANS_NUM
and TRANSACTION_TYPE='DIST' and PRODUCT_CODE=@ProductCode
group by ACTIVITY_SEQN
END
begin transaction
insert into Trans_SoftCredit(TRANS_NUMBER,SOFT_CREDIT_ID,PERCENTAGE,AMOUNT,ORIGINATING_ACTIVITY_SEQN,PRODUCT_CODE)
values(@TransNumber,@StId,@Percent,@Amount,@ActivitySeqn,@ProductCode)
update Trans set ST_ID=@BtId where TRANS_NUMBER=@TransNumber
commit transaction
END
ELSE
BEGIN
truncate table #SplitGiftRows
insert into #SplitGiftRows(TransNumber,StId,Amount,ActivitySeqn,ProductCode)
select max(ORIGINATING_TRANS_NUM),max(OTHER_ID),sum(AMOUNT),max(SEQN),max(PRODUCT_CODE) from Activity
where ORIGINATING_TRANS_NUM=@TransNumber
group by ORIGINATING_TRANS_NUM,PRODUCT_CODE
declare Get_Dist cursor for
select TransNumber,StId,Amount,ActivitySeqn,ProductCode from #SplitGiftRows
open Get_Dist
fetch next from Get_Dist into @TransNumber,@StId,@DistAmount,@ActivitySeqn,@ProductCode
WHILE @@FETCH_STATUS = 0
BEGIN
if @DistAmount<>0
BEGIN
select @ActivitySeqn=max(ACTIVITY_SEQN) from Trans
join Invoice on REFERENCE_NUM=@InvRefNum
where TRANS_NUMBER=ORIGINATING_TRANS_NUM
and TRANSACTION_TYPE='DIST' and PRODUCT_CODE=@ProductCode
group by ACTIVITY_SEQN
END
insert into Trans_SoftCredit(TRANS_NUMBER,SOFT_CREDIT_ID,PERCENTAGE,AMOUNT,ORIGINATING_ACTIVITY_SEQN,PRODUCT_CODE)
values(@TransNumber,@StId,@Percent,@DistAmount,@ActivitySeqn,@ProductCode)
fetch next from Get_Dist into @TransNumber,@StId,@DistAmount,@ActivitySeqn,@ProductCode
END
close Get_Dist
deallocate Get_Dist
update Trans set ST_ID=@BtId where TRANS_NUMBER=@TransNumber
END
fetch next from Get_Trans into @TransNumber,@BtId,@StId,@Amount,@TotalAmount,@ActivitySeqn,@ProductCode,@InvRefNum,@JournalType
END
close Get_Trans
deallocate Get_Trans

GO
GRANT EXECUTE ON  [dbo].[sp_asi_UpdateTransSoftCredit] TO [IMIS]
GO